package whf.framework.tools.generator.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import org.apache.commons.lang.StringUtils;

import whf.framework.resource.db.ConnectionManager;

/**
 * @author wanghaifeng
 *
 */
public class MysqlTable extends Table{
	
	public MysqlTable(ConnectionManager cm,String tableSchema, String name, String tablePre, String tableComment) throws Exception{
		super(cm, tableSchema, name, tablePre, tableComment);
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			con = cm.getPooledConnection();
			pstmt = con.prepareStatement("select t1.data_type,t1.column_name,t1.column_comment,t1.is_nullable,t1.character_maximum_length from columns t1 where t1.table_schema=? and t1.table_name=? ");
			pstmt.setString(1, this.getTableSchema());
			pstmt.setString(2, this.getTableName());
			rs = pstmt.executeQuery();
			while(rs.next()){
				String dataType = rs.getString("data_type");
				String columnName = rs.getString("column_name");
				String columnComment = rs.getString("column_comment");
				boolean nullable = rs.getBoolean("is_nullable");
				int maxLength = rs.getInt("character_maximum_length");
				this.addColumn(new Column(this, columnName, dataType, columnComment, nullable, maxLength));
			}
			rs.close();
			pstmt.close();
			//decide primarykey foreign key and unique property
			pstmt = con.prepareStatement("select t1.column_name,t2.constraint_type,t1.referenced_table_name,t1.referenced_column_name from KEY_COLUMN_USAGE t1 inner join TABLE_CONSTRAINTS t2 on t1.table_name=t2.table_name and t1.table_schema=t2.table_schema where t1.table_schema=? and t1.table_name=?");
			pstmt.setString(1, this.getTableSchema());
			pstmt.setString(2, this.getTableName());
			rs = pstmt.executeQuery();
			while(rs.next()){
				String columnName = rs.getString(1);
				String constraintType = rs.getString(2);
				String referenceTableName = rs.getString(3);
				String referenceColumnName = rs.getString(4);
				
				Column col = findColumnByName(columnName);
				if(col != null){
					if(StringUtils.equalsIgnoreCase(constraintType, "primary key")){
						col.setPrimaryKey(true);
					}else if(StringUtils.equalsIgnoreCase(constraintType,"foreign key")){
						col.setForeign(true);
						col.setReferencedColumn(referenceColumnName);
						col.setReferencedTable(referenceTableName);
					}else if(StringUtils.equalsIgnoreCase("unique", constraintType)){
						col.setUnique(true);
					}
				}
			}
			//重新判断外键列是否是主键
			for(int i=0;i<this.getColumns().size();i++){
				Column col = (Column)this.getColumns().get(i);
				if(!col.isForeign()) continue;
				pstmt = con.prepareStatement("select t1.column_name,t2.constraint_type,t1.referenced_table_name,t1.referenced_column_name from KEY_COLUMN_USAGE t1 inner join TABLE_CONSTRAINTS t2 on t1.table_name=t2.table_name and t1.table_schema=t2.table_schema where t2.constraint_type='primary key' and t1.table_schema=? and t1.table_name=? and t1.column_name=?");
				pstmt.setString(1, this.getTableSchema());
				pstmt.setString(2,col.getReferencedTable());
				pstmt.setString(3, col.getReferencedColumn());
				rs = pstmt.executeQuery();
				if(!rs.next()){
					col.setForeign(false);
				}
			}
		}finally{
			if(rs != null) rs.close();
			if(pstmt != null) pstmt.close();
			if(con != null) con.close();
		}
	}
}
